package com.construction.engineering.accounting.common.until;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.util.HashMap;
import java.util.Map;
import java.util.Optional;

public class ExcelUntil {

    /**
     * 设置样式
     *
     * @param workbook  workbook
     * @param bold      是否加粗 boolean
     * @param colorStr  背景颜色 --- IndexedColors.BRIGHT_GREEN.getIndex()
     * @param fontColor 字体颜色 --- IndexedColors.BLACK.getIndex()
     * @return return
     */
    public static CellStyle getStyle(Workbook workbook, boolean bold, Short colorStr, Short fontColor) {
        // 创建样式
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);
        font.setBold(bold); //加粗
        font.setColor(fontColor); // 字体颜色为黑色
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER_SELECTION);
        // 设置边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        if (colorStr != null && colorStr > -1) {
            style.setFillForegroundColor(colorStr);
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 确保设置填充模式
        }

        return style;
    }

    /**
     * 获取一个cell
     *
     * @param sheet sheet
     * @param row   行
     * @param col   列
     * @param style 样式
     * @return cell
     */
    public static Cell getCell(Sheet sheet, int row, int col, CellStyle style) {

        Row sheetRow = sheet.getRow(row);
        if (sheetRow == null) {
            sheetRow = sheet.createRow(row);
        }
        Cell cell = sheetRow.getCell((short) col);
        if (cell == null) {
            cell = sheetRow.createCell((short) col);
        }
        cell.setCellStyle(style);
        return cell;
    }

    /**
     * 写入cell内容
     *
     * @param row   行
     * @param col   列
     * @param sheet sheet
     * @param style 样式
     * @param value 值
     */
    public static void writeStringCell(int row, int col, Sheet sheet, CellStyle style, String value) {
        // 创建单元格并设置样式
        Cell cell;
        sheet.setDefaultColumnWidth((short) 30);
        //写入对应的cell
        cell = getCell(sheet, row, col, style);
        cell.setCellValue(value);
    }

    /**
     * 读取单元格字符串
     * @param row row
     * @param col col
     * @return string
     */
    public static String readStringCell(Row row, int col) {
        Cell cell = row.getCell((short) col);
        if (cell == null) {
            return "";
        }
        return cell.getStringCellValue();
    }

    /**
     * 带类型转换的读取方法
     *
     * @throws ClassCastException 如果类型不匹配
     *                            readCellValueAs("sheet", 1, 1, String.class);
     */
    public static <T> T readCellValueAs(Sheet sheet, int rowNum, int colNum, Class<T> type) throws Exception {
        Object value = readCellValue(sheet, rowNum, colNum);
        return type.cast(value);
    }


    /**
     * 通用读取方法（返回Object）
     *
     * @param sheet  sheet
     * @param rowNum 行
     * @param colNum 列
     * @return object
     */
    public static Object readCellValue(Sheet sheet, int rowNum, int colNum) throws Exception {
        return Optional.ofNullable(sheet.getRow(rowNum))
                .map(row -> row.getCell(colNum, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL))
                .map(ExcelUntil::convertCellValue)
                .orElse(null);
    }

    /**
     * 根据不通文件后缀获取不通的workbook
     *
     * @param filePath 路径
     * @param fis      FileInputStream
     * @return workbook
     */
    public static Workbook getWorkbook(String filePath, FileInputStream fis) throws Exception {
        // 根据文件扩展名选择 Workbook 实现
        if (filePath.endsWith(".xlsx")) {
            return new XSSFWorkbook(fis); // .xlsx 文件
        } else if (filePath.endsWith(".xls")) {
            return new HSSFWorkbook(fis); // .xls 文件
        } else {
            throw new IllegalArgumentException("Unsupported file format. Only .xls or .xlsx are supported.");
        }
    }

    /**
     * 获取表头
     *
     * @param sheet_sheet sheet
     * @return map
     */
    public static Map<String, Integer> getHeadMap(Sheet sheet_sheet) {
        try {
            Map<String, Integer> map = new HashMap<>();
            for (Row row : sheet_sheet) {
                if (row.getRowNum() == 0) {
                    int i = 0;//标记列
                    for (Cell cell : row) {
                        String value = cell.getStringCellValue();
                        map.put(value, i);
                        i++;
                    }
                    return map;
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return null;
    }


    /**
     * 类型安全的单元格值转换
     */
    private static Object convertCellValue(Cell cell) {
        return switch (cell.getCellType()) {
            // 字符串类型
            case STRING -> cell.getStringCellValue().trim();

            // 数字类型（可能是Double或Date）
            case NUMERIC -> DateUtil.isCellDateFormatted(cell) ?
                    cell.getLocalDateTimeCellValue() :
                    cell.getNumericCellValue();

            // 布尔类型
            case BOOLEAN -> cell.getBooleanCellValue();

            // 公式类型（获取计算结果）
            case FORMULA -> convertFormulaCell(cell);

            // 空单元格
            case BLANK, _NONE -> null;

            // 其他未知类型
            default -> throw new IllegalStateException("未知单元格类型: " + cell.getCellType());
        };
    }

    /**
     * 处理公式单元格
     */
    private static Object convertFormulaCell(Cell cell) {
        return switch (cell.getCachedFormulaResultType()) {
            case STRING -> cell.getStringCellValue().trim();
            case NUMERIC -> DateUtil.isCellDateFormatted(cell) ?
                    cell.getLocalDateTimeCellValue() :
                    cell.getNumericCellValue();
            case BOOLEAN -> cell.getBooleanCellValue();
            default -> cell.getCellFormula(); // 无法计算时返回公式本身
        };
    }

}
